package SVN::TeamTools::Store::StatsDB;

use warnings;
use strict;
use Carp;
use Error qw(:try);

use Data::Dumper;

use SVN::TeamTools::Store::Config;
use SVN::TeamTools::Store::Repo;

use DBI;

my $conf;
my $logger;
BEGIN { $conf = SVN::TeamTools::Store::Config->new(); $logger = $conf->{logger}; }

sub hasAction {
        shift;
        my %args        = @_;
        my $action      = $args{action};
        return ("|dsp.stats|get.stats.lines|get.stats.commits|get.stats.files|get.stats.topdev.lines|get.stats.topdev.commits|get.stats.topdev.files|get.stats.project.lines|get.stats.project.commits|get.stats.project.files|get.stats.project.activity|get.stats.revisions|get.stats.project.changes|" =~ /\|\Q$action\E\|/);
}

sub getTemplate {
	my $self	= shift;
	my $stats	= $self->new(create=>0);
	my $template	= HTML::Template->new( filename => 'SVN/TeamTools/Store/tmpl/stats.tmpl', path => @INC );
	my $list = "";
	my $stmnt = "SELECT DISTINCT name FROM projects ORDER BY name";
	try {
		my $sth         = $stats->{dbh}->prepare($stmnt); $sth->execute();
		while( my $project = $sth->fetchrow()) {
			$list="$list<option>$project</option>";
		}
	} otherwise {
		my $exc = shift; croak "Error fetching data,  error: $exc";
	};
	
	$template->param (p_proj => $list);
	return $template;
}

sub new {
	my $class	= shift;
	my %args	= @_;
	my $create	= $args{create};

	my $self = {};
	try {
		$self = {
			dbh	=> DBI->connect("dbi:SQLite:dbname=" . $conf->{svnstats},"",""),
			repo	=> SVN::TeamTools::Store::Repo->new(),
		};
		
        } otherwise {
                my $exc = shift;
                croak "Error opening database with path $conf->{svnstats} error: $exc";
        };

	if ($create) {
		try {
			$self->{dbh}->do("DROP TABLE IF EXISTS revisions");
			$self->{dbh}->do("CREATE TABLE revisions (id INTEGER PRIMARY KEY, rev INTEGER, revdate DATE, author TEXT, files_added INTEGER, files_deleted INTEGER, files_updated INTEGER, lines_added INTEGER, lines_deleted INTEGER)");
			$self->{dbh}->do("CREATE UNIQUE INDEX revision_idx1 ON revisions(rev)");
			$self->{dbh}->do("CREATE INDEX revision_idx2 ON revisions(revdate, author)");
			$self->{dbh}->do("CREATE INDEX revision_idx3 ON revisions(author, revdate)");

			$self->{dbh}->do("DROP TABLE IF EXISTS projects");
			$self->{dbh}->do("CREATE TABLE projects (path TEXT, name TEXT, startrelease INTEGER, startdate DATE, lastrev INTEGER, lastupdate DATE, endrevision DATE DEFAULT NULL, files_added INTEGER, files_deleted INTEGER, files_updated INTEGER, lines_added INTEGER, lines_deleted INTEGER)");
			$self->{dbh}->do("CREATE UNIQUE INDEX project_idx1 ON projects(path, endrevision)");
			$self->{dbh}->do("CREATE INDEX project_idx2 ON projects(endrevision)");

			$self->{dbh}->do("DROP TABLE IF EXISTS projectrevs");
			$self->{dbh}->do("CREATE TABLE projectrevs (path TEXT, rev INTEGER)");
			$self->{dbh}->do("CREATE UNIQUE INDEX projectrev_idx1 ON projectrevs(path, rev)");
			$self->{dbh}->do("CREATE INDEX projectrev_idx2 ON projectrevs(rev, path)");
			$self->{dbh}->do("CREATE TRIGGER project_trg AFTER UPDATE OF lastrev ON projects FOR EACH ROW BEGIN INSERT INTO projectrevs VALUES (new.path,new.lastrev); END;");
        	} otherwise {
	                my $exc = shift;
	                croak "Error creating tables structure,  error: $exc";
        	};
	}

	bless $self, $class;
	return $self;
}

sub addRev {
	my $self	= shift;
	my %args	= @_;
	my $rev		= $args{rev};

	my $look	= $self->{repo}->getLook(rev => $rev);
	$look->date()	=~ /^(\d+-\d+-\d+\s\d+:\d+:\d+)\s/;
	my $date	= $1;
	my @chk_added	= $look->added();
	my @chk_updated	= $look->updated();


	my $lines_added	= 0; 
	my $lines_deleted = 0;
	if ((scalar (@chk_added)>0 and $chk_added[0] =~ /branches/) or (scalar (@chk_updated)>0 and $chk_updated[0] =~ /branches/)) {
		my @lines 	= $look->diff ("--no-diff-deleted");
		foreach my $line (@lines) {
			if ($line =~ /^\+[^\+]/) { $lines_added++;}
			if ($line =~ /^-[^-]/) { $lines_deleted++;}
		}
	}

	try {
		$self->{dbh}->do("INSERT INTO revisions(rev,revdate,author,files_added,files_deleted,files_updated,lines_added,lines_deleted) VALUES ($rev, date('$date'), \"".$look->author()."\", ".scalar($look->added()).", ".scalar($look->deleted()).", ".scalar($look->updated()).", $lines_added, $lines_deleted)");
	} otherwise {
		my $exc = shift;
		croak "Error inserting data,  error: $exc";
        };

	my @deleted = $look->deleted();
	if (scalar(@deleted) == 1) {
		$logger->debug("Maybe deleted: $deleted[0]");
		try {
			$self->{dbh}->do("UPDATE projects SET endrevision=$rev WHERE path=\"$deleted[0]\" AND endrevision IS NULL");
		} otherwise {
			my $exc = shift;
			croak "Error decommisioning project $deleted[0],  error: $exc";
	        };
	}

	my @copied_from = $look->copied_from();
	my @copied_to = $look->copied_to();

	if (scalar(@copied_from) == 1) {

		$copied_to[0] =~ /.*\/([^\/]+)\//;
		my $projname=$1;
			
		if ($copied_from[0] =~ /trunk\/$/) {
			$logger->debug ("Analyzing commits, found new project (path: $copied_to[0], revision: $rev)");
			try {
				$self->{dbh}->do("INSERT INTO projects(path,name,startrelease,startdate,lastrev,lastupdate,files_added,files_deleted,files_updated,lines_added,lines_deleted) VALUES (\"$copied_to[0]\",\"$projname\",$rev, date('$date'),$rev,date('$date'), 0,0,0,0,0)");
		        } otherwise {
				my $exc = shift;
				croak "Error inserting data for project $copied_to[0],  error: $exc";
		        };
		} else {
			try {
				my $rows = $self->{dbh}->do("UPDATE projects SET path=\"" . $copied_to[0] . "\",name=\"$projname\",lastrev=$rev WHERE path=\"" . $copied_from[0] . "\"");

				if ( $rows == 1 ) {
					$logger->debug ("Analyzing commits, project renamed (new path: $copied_to[0], old path: $copied_from[0], revision: $rev)");
				}
		        } otherwise {
				my $exc = shift;
				croak "Error updating data while renaming project $copied_from[0],  error: $exc";
		        };
		}
	} else {
		my @changelist = $look->changed();
		if (scalar(@changelist)>0) {
			try {
				$self->{dbh}->do("UPDATE projects SET lastrev=$rev, lastupdate=date('$date'), files_added=files_added+".scalar($look->added()).",files_deleted=files_deleted+".scalar($look->deleted()).",files_updated=files_updated+".scalar($look->updated()).",lines_added=lines_added+$lines_added,lines_deleted=lines_deleted+$lines_deleted WHERE endrevision IS NULL AND \"$changelist[0]\" LIKE path||'%'");
		        } otherwise {
				my $exc = shift;
				croak "Error updating data for project containing $changelist[0],  error: $exc";
		        };
		}
	}
}

sub lastRev {
	my $self	= shift;

	try {
		my $sth		= $self->{dbh}->prepare("SELECT MAX(rev) FROM revisions");
		$sth->execute();
		my ($rev)	= $sth->fetchrow();

		return $rev;
        } otherwise {
		my $exc = shift;
		croak "Error fetching data,  error: $exc";
        };
}
sub limitTick {
	my $name	= $_[0];
	if (length($name) >15) {
		$name=substr($name,0,11) . "...";
	}
	return $name;
}

sub getData {
	my $self	= shift;
	my %args	= @_;
	my $action	= $args{action};
	my $param	= $args{param};

	my $stats	= $self->new(create=>0);

	my @fd		= split('-',$param->{fdate});
	my @td		= split('-',$param->{tdate});

	my $daterange	= sprintf(" BETWEEN date('%04d-%02d-%02d') AND date('%04d-%02d-%02d')",$fd[2],$fd[1],$fd[0],$td[2],$td[1],$td[0]);

	my %result;
	if ($action =~ /get.stats.lines/ ) {
		my @data1;
		my @data2;
		my $stmnt = "	SELECT ROUND(julianday(revdate)-julianday('1970-01-01')) day, sum(lines_added) la, sum(lines_deleted) ld 
				FROM revisions 
				WHERE revdate $daterange
				GROUP BY ROUND(julianday(revdate))";
		try {
			my $sth		= $stats->{dbh}->prepare($stmnt); $sth->execute();
			while( my ($day, $la, $ld) = $sth->fetchrow()) {
				push(@data1, [$day*60*60*24*1000, $la]);
				push(@data2, [$day*60*60*24*1000, $ld]);
			}
			%result=(data => [{label=>"added", data=>\@data1}, {label=>"deleted", data=>\@data2}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc";
		};
	} elsif ($action =~ /get.stats.files/ ) {
		my @data1;
		my @data2;
		my @data3;
		my $stmnt = "	SELECT ROUND(julianday(revdate)-julianday('1970-01-01')) day, sum(files_added) fa, sum(files_deleted) fd , sum(files_updated) fu
				FROM revisions 
				WHERE revdate $daterange
				GROUP BY ROUND(julianday(revdate))";
		try {
			my $sth		= $stats->{dbh}->prepare($stmnt); $sth->execute();
			while( my ($day, $fa, $fd, $fu) = $sth->fetchrow()) {
				push(@data1, [$day*60*60*24*1000, $fa]);
				push(@data2, [$day*60*60*24*1000, $fd]);
				push(@data3, [$day*60*60*24*1000, $fu]);
			}
			%result=(data => [{label=>"added", data=>\@data1}, {label=>"deleted", data=>\@data2}, {label=>"updated", data=>\@data3}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc";
		};
	} elsif ($action =~ /get.stats.commits/ ) {
		my @data1;
		my $stmnt = "	SELECT ROUND(julianday(revdate)-julianday('1970-01-01')) day, count(*) cmt 
				FROM revisions 
				WHERE revdate $daterange
				GROUP BY ROUND(julianday(revdate))";
		try {
			my $sth		= $stats->{dbh}->prepare($stmnt); $sth->execute();
			while( my ($day, $cmt) = $sth->fetchrow()) {
				push(@data1, [$day*60*60*24*1000, $cmt]);
			}
			%result=(data => [{label=>"commits", data=>\@data1}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.topdev.commits/ ) {
		my @ticks;
		my @data1;
		my $stmnt = "	SELECT IFNULL(NULLIF(author,''),'unknown') author, cmt 
				FROM (
					SELECT	author,count(*) cmt
					FROM	revisions 
					WHERE	revdate $daterange AND author NOT IN ('jenkins','root')
					GROUP BY author
					ORDER BY cmt DESC
					LIMIT 20)
				ORDER BY cmt";
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt); $sth->execute();
			my $i	=1;
			while( my ($author, $cmt) = $sth->fetchrow()) {
				push(@data1, [$cmt, $i]); 
				push(@ticks, [$i+.5, limitTick($author)]);
				$i++;
			}
			%result=(ticks => \@ticks, data => [{label=>"commits", data=>\@data1}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.topdev.lines/ ) {
		my @ticks;
		my @data1;
		my @data2;
		my $stmnt = "	SELECT IFNULL(NULLIF(author,''),'unknown') author, la, ld
				FROM (
					SELECT	author,sum(lines_added) la, sum(lines_deleted) ld 
					FROM	revisions 
					WHERE	revdate $daterange AND author NOT IN ('jenkins','root')
					GROUP BY author
					ORDER BY la+ld DESC
					LIMIT 20)
				ORDER BY la+ld";
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt); $sth->execute();
			my $i	=1;
			while( my ($author, $la, $ld) = $sth->fetchrow()) {
				push(@data1, [$la, $i]); 
				push(@data2, [$ld, $i]); 
				push(@ticks, [$i+.5, limitTick($author)]);
				$i++;
			}

			%result=(ticks => \@ticks, data => [{label=>"added", data=>\@data1}, {label=>"deleted", data=>\@data2}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.topdev.files/ ) {
		my @ticks;
		my @data1;
		my @data2;
		my @data3;
		my $stmnt = "	SELECT IFNULL(NULLIF(author,''),'unknown') author, fa, fd, fu
				FROM (
					SELECT	author,sum(files_added) fa, sum(files_deleted) fd, sum(files_updated) fu
					FROM	revisions 
					WHERE	revdate $daterange AND author NOT IN ('jenkins','root')
					GROUP BY author
					ORDER BY fa+fd+fu DESC
					LIMIT 20)
				ORDER BY fa+fd+fu";
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt); $sth->execute();
			my $i	=1;
			while( my ($author, $fa, $fd, $fu) = $sth->fetchrow()) {
				push(@data1, [$fa, $i]); 
				push(@data2, [$fd, $i]); 
				push(@data3, [$fu, $i]); 
				push(@ticks, [$i+.5, limitTick($author)]);
				$i++;
			}
			%result=(ticks => \@ticks, data => [{label=>"added", data=>\@data1},{label=>"deleted", data=>\@data2},{label=>"updated", data=>\@data3}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.project.lines/ ) {
		my @ticks;
		my @data1;
		my @data2;
		my $stmnt = "	SELECT	name,lines_added la, lines_deleted ld 
				FROM	projects
				WHERE	startdate $daterange AND lastupdate $daterange
				ORDER BY lines_added+lines_deleted"; 
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt) or die "SQL error ".$stats->{dbh}->errstr; 
			$sth->execute();
			my $i	=1;
			while( my ($name, $la, $ld) = $sth->fetchrow()) {
				push(@data1, [$la, $i]); 
				push(@data2, [$ld, $i]); 
				push(@ticks, [$i+.5, limitTick($name)]);
				$i++;
			}
			%result=(ticks => \@ticks, data => [{label=>"added", data=>\@data1}, {label=>"deleted", data=>\@data2}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.project.files/ ) {
		my @ticks;
		my @data1;
		my @data2;
		my @data3;
		my $stmnt = "	SELECT	name as project,files_added fa, files_deleted fd, files_updated fu
				FROM	projects
				WHERE	startdate $daterange AND lastupdate $daterange
				ORDER BY files_added+files_updated+files_deleted"; 
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt); $sth->execute();
			my $i	=1;
			while( my ($name, $fa, $fd, $fu) = $sth->fetchrow()) {
				push(@data1, [$fa, $i]); 
				push(@data2, [$fd, $i]); 
				push(@data3, [$fu, $i]); 
				push(@ticks, [$i+.5, limitTick($name)]);
				$i++;
			}
			%result=(ticks => \@ticks, data => [{label=>"added", data=>\@data1},{label=>"deleted", data=>\@data2},{label=>"updated", data=>\@data3}]);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.project.activity/ ) {
		my @project;
		my @ticks;
		my @data1;
		my $stmnt = "	SELECT name , ROUND(julianday(startdate)-julianday('1970-01-01')) fday, ROUND(julianday(lastupdate)-julianday('1970-01-01')) lday, name||' ('||startdate||' to '||lastupdate||')' desc
				FROM	projects
				WHERE	startdate $daterange AND lastupdate $daterange"; 
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt); $sth->execute();
			my $i 	= 1;
			while( my ($name, $fday, $lday, $desc) = $sth->fetchrow()) {
				push(@data1, [$lday*60*60*24*1000, $i, $fday*60*60*24*1000]);
				push(@ticks, [$i+.5, limitTick($name)]);
				push(@project, $desc);
				$i++;
			}
			%result=(ticks => \@ticks, data => [{data=>\@data1}], project=>\@project);

		 } otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};
	} elsif ($action =~ /get.stats.project.changes/ ) {
		my $project	= $param->{project};
		my @changes;
		my $stmnt = "	SELECT pr.rev, length(p.path) ln FROM projects p, projectrevs pr WHERE p.path=pr.path and p.name=\"$project\" ORDER BY pr.rev";
		try {
			my $sth	= $stats->{dbh}->prepare($stmnt); $sth->execute();
			while( my ($rev, $ln) = $sth->fetchrow()) {
				my $look	= $stats->{repo}->getLook(rev => $rev);
				foreach my $line ($look->added()) {
					my %out=(rev=>$rev, action=>"added", path=>$line, dsppath=>substr($line,$ln));
					push(@changes,\%out);
				}
				foreach my $line ($look->deleted()) {
					my %out=(rev=>$rev, action=>"deleted", path=>$line, dsppath=>substr($line,$ln));
					push(@changes,\%out);
				}
				foreach my $line ($look->updated()) {
					my %out=(rev=>$rev, action=>"updated", path=>$line, dsppath=>substr($line,$ln));
					push(@changes,\%out);
				}
			}
		} otherwise {
			my $exc = shift; croak "Error fetching data,  error: $exc,\n$stmnt";
		};

		%result=(changes => \@changes);
		
	}
	return \%result;
}
1;
